import numpy as np
from pyhive import hive
import pandas as pd
import matplotlib.pyplot as plt


def hive_read_hql(sql_code, connection):
    cur = connection.cursor()
    cur.execute(sql_code)
    headers = [col[0] for col in cur.description]
    df = pd.DataFrame(cur.fetchall(), columns=headers)
    cur.close()
    return df


def hive_exec_hql(sql_code, connection):
    cur = connection.cursor()
    cur.execute(sql_code)
    cur.close()


def hive_connect():
    # hive 的 ip 地址要更换成你自己的
    conn = hive.connect(host='10.251.253.183', port=30000)

    hql = 'show databases'
    df = hive_read_hql(hql, conn)

    print(df)

    # 开启动态分区，默认是 false
    hive_exec_hql('set hive.exec.dynamic.partition=true', conn)
    # 允许所有分区都是动态的，否则必须要有静态分区才能使用
    hive_exec_hql('set hive.exec.dynamic.partition.mode=nonstrict', conn)
    # 尝试使用本地模式执行其他的操作
    hive_exec_hql('set hive.exec.mode.local.auto=true', conn)
    # 设置允许的动态分区的最大数量
    hive_exec_hql('set hive.exec.max.dynamic.partitions=1000', conn)
    hive_exec_hql('set hive.exec.max.dynamic.partitions.pernode=1000', conn)
    # 设置本地模式运行 mapreduce
    hive_exec_hql('set mapreduce.framework.name=local', conn)

    create_table_life_hql = '''
    create table life
    (
    Country string,
    `Year` int,
    Status string,
    Life_expectancy float,
    Adult_Mortality int,
    infant_deaths int,
    Alcohol int,
    percentage_expenditure float,
    HepatitisB int,
    Measles int,
    BMI float,
    under_five_death int,
    Polio int,
    Total_expenditure float,
    Diphtheria int,
    HIV_AIDS float,
    GDP float,
    Population float,
    thinness_under_19 float,
    thinness_over_19 float,
    Income_composition_of_resources float,
    Schooling float
    )
    row format delimited fields terminated by ','
    '''

    hive_exec_hql(create_table_life_hql, conn)
    # 从 csv 文件导入数据
    hive_exec_hql("load data local inpath '/opt/hive/exp/life.csv' into table life", conn)

    hql = 'select * from life'
    df = hive_read_hql(hql, conn)

    print(df)

    create_table_part3_hql = '''
    create table part3
    (
    Country string,
    `Year` int,
    `Status` string,
    Life_expectancy float,
    Adult_Mortality int,
    infant_deaths int,
    Alcohol int,
    percentage_expenditure float,
    HepatitisB int,
    Measles int,
    BMI float,
    under_five_death int,
    Polio int,
    Total_expenditure float,
    Diphtheria int,
    HIV_AIDS float,
    GDP float,
    Population float,
    thinness_under_19 float,
    thinness_over_19 float,
    Income_composition_of_resources float,
    Schooling float
    )
    partitioned by (period int)
    row format delimited fields terminated by ','
    '''

    hive_exec_hql(create_table_part3_hql, conn)

    hive_exec_hql('insert overwrite table default.part3 partition(period) select *, Year from life', conn)
    pass


def data_exp():
    conn = hive.connect(host='10.251.253.183', port=30000)

    # query_hql = ('select avg(Life_expectancy) as avg_life, avg(Adult_Mortality) as avg_adult_mortality, '
    #              'avg(infant_deaths) as avg_infant_deaths, avg(Schooling) as avg_schooling, '
    #              'avg(BMI) as avg_BMI, avg(Alcohol) as avg_alcohol, avg(under_five_death) as avg_under_five_death, '
    #              'avg(thinness_under_19) as avg_thinness_under_19, avg(thinness_over_19) as avg_thinness_over_19, '
    #              'avg(Population) as avg_population, avg(GDP) as avg_GDP, '
    #              'avg(HepatitisB) as avg_hepatitisB, avg(Polio) as avg_polio, avg(Diphtheria) as avg_diphtheria, '
    #              'avg(HIV_AIDS) as avg_HIV_AIDS, avg(Measles) as avg_measles, '
    #              'avg(Total_expenditure) as avg_total_expenditure, avg(percentage_expenditure) as avg_percentage_expenditure, '
    #              'avg(Income_composition_of_resources) as avg_income_composition_of_resources, '
    #              'Year as year, Status as status from part3 group by Year, Status')
    # df = hive_read_hql(query_hql, conn)
    # df.index = df["year"]
    # df = df.sort_index(ascending=True)
    # groups = df.groupby(df["status"])
    #
    # developing_df = groups.get_group("Developing")
    # developed_df = groups.get_group("Developed")
    #
    # # 发达国家和发展中国家的预期寿命随时间的变化情况  Life_expectancy(developed and developing)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)  # 1行1列第1个
    # ax = developing_df.plot(x='year', kind='line', y='avg_life', label='Developing', ax=ax)
    # developed_df.plot(x='year', kind='line', y='avg_life', label='Developed', ax=ax)
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy(developed and developing)')
    # # 添加x轴和y轴标签
    # plt.xlabel('year')
    # plt.ylabel('avg_life')
    # plt.show()
    #
    # # 发达国家的预期寿命和死亡率随时间的变化情况  死亡率(developed)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # # ax = developed_df.plot(x='year', kind='line', y='avg_adult_mortality', label='avg_adult_mortality', ax=ax)
    # ax = developed_df.plot(x='year', kind='line', y='avg_infant_deaths', label='avg_infant_deaths', ax=ax)
    # # ax = developed_df.plot(x='year', kind='line', y='avg_under_five_death', label='avg_under_five_death', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developed_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and Infant_deaths(developed)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('infant_deaths')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发展中国家的预期寿命和死亡率随时间的变化情况  死亡率(developing)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # # ax = developing_df.plot(x='year', kind='line', y='avg_adult_mortality', label='avg_adult_mortality', ax=ax)
    # ax = developing_df.plot(x='year', kind='line', y='avg_infant_deaths', label='avg_infant_deaths', ax=ax)
    # # ax = developing_df.plot(x='year', kind='line', y='avg_under_five_death', label='avg_under_five_death', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developing_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and Infant_deaths(developing)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('infant_deaths')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发达国家的预期寿命和HIV_AIDS随时间的变化情况  HIV_AIDS(developed)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developed_df.plot(x='year', kind='line', y='avg_hiv_aids', label='avg_HIV_AIDS', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developed_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and HIV_AIDS(developed)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('HIV_AIDS')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发展中国家的预期寿命和HIV_AIDS随时间的变化情况  HIV_AIDS(developing)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developing_df.plot(x='year', kind='line', y='avg_hiv_aids', label='avg_HIV_AIDS', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developing_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and HIV_AIDS(developing)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('HIV_AIDS')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发达国家的预期寿命和麻疹病例数随时间的变化情况  measles(developed)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developed_df.plot(x='year', kind='line', y='avg_measles', label='avg_measles', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developed_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and Measles(developed)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('measles')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发展中国家的预期寿命和麻疹病例数随时间的变化情况  measles(developing)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developing_df.plot(x='year', kind='line', y='avg_measles', label='avg_measles', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developing_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and Measles(developing)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('measles')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发达国家的预期寿命和免疫接种率随时间的变化情况  免疫接种率(developed)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developed_df.plot(x='year', kind='line', y='avg_hepatitisb', label='avg_hepatitisB', ax=ax)
    # ax = developed_df.plot(x='year', kind='line', y='avg_polio', label='avg_polio', ax=ax)
    # ax = developed_df.plot(x='year', kind='line', y='avg_diphtheria', label='avg_diphtheria', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developed_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and Immunization coverage(developed)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('Immunization coverage')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发展中国家的预期寿命和免疫接种率随时间的变化情况  免疫接种率(developing)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developing_df.plot(x='year', kind='line', y='avg_hepatitisb', label='avg_hepatitisB', ax=ax)
    # ax = developing_df.plot(x='year', kind='line', y='avg_polio', label='avg_polio', ax=ax)
    # ax = developing_df.plot(x='year', kind='line', y='avg_diphtheria', label='avg_diphtheria', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developing_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and Immunization coverage(developing)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('Immunization coverage')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发达国家的预期寿命和瘦弱率随时间的变化情况  thinness_under_19 and thinness_over_19(developed)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developed_df.plot(x='year', kind='line', y='avg_thinness_under_19', label='avg_thinness_under_9', ax=ax)
    # ax = developed_df.plot(x='year', kind='line', y='avg_thinness_over_19', label='avg_thinness_over_9', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developed_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and Emaciation rate(developed)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('Emaciation rate')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发展中国家的预期寿命和瘦弱率随时间的变化情况  thinness_under_19 and thinness_over_19(developing)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developing_df.plot(x='year', kind='line', y='avg_thinness_under_19', label='avg_thinness_under_9', ax=ax)
    # ax = developing_df.plot(x='year', kind='line', y='avg_thinness_over_19', label='avg_thinness_over_9', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developing_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and Emaciation rate(developing)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('Emaciation rate')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发达国家的预期寿命和BMI随时间的变化情况  BMI(developed)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developed_df.plot(x='year', kind='line', y='avg_bmi', label='avg_BMI', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developed_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and BMI(developed)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('BMI')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发展中国家的预期寿命和BMI随时间的变化情况  BMI(developing)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developing_df.plot(x='year', kind='line', y='avg_bmi', label='avg_BMI', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developing_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and BMI(developing)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('BMI')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发达国家的预期寿命和酒精消费量随时间的变化情况  Alcohol(developed)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developed_df.plot(x='year', kind='line', y='avg_alcohol', label='avg_alcohol', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developed_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and alcohol(developed)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('alcohol')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发展中国家的预期寿命和酒精消费量随时间的变化情况  Alcohol(developing)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developing_df.plot(x='year', kind='line', y='avg_alcohol', label='avg_alcohol', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developing_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and alcohol(developing)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('alcohol')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发达国家的预期寿命和教育年限随时间的变化情况   Schooling(developed)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developed_df.plot(x='year', kind='line', y='avg_schooling', label='avg_schooling', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developed_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and schooling(developed)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('schooling')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发展中国家的预期寿命和教育年限随时间的变化情况  Schooling(developing)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developing_df.plot(x='year', kind='line', y='avg_schooling', label='avg_schooling', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developing_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and schooling(developing)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('schooling')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发达国家的预期寿命和人口随时间的变化情况  Population(developed)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developed_df.plot(x='year', kind='line', y='avg_population', label='avg_population', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developed_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and population(developed)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('population')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发展中国家的预期寿命和人口随时间的变化情况  Population(developing)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developing_df.plot(x='year', kind='line', y='avg_population', label='avg_population', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developing_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and population(developing)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('population')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发达国家的预期寿命和GDP随时间的变化情况  GDP(developed)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developed_df.plot(x='year', kind='line', y='avg_gdp', label='avg_GDP', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developed_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and GDP(developed)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('GDP')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发展中国家的预期寿命和GDP随时间的变化情况  GDP(developing)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developing_df.plot(x='year', kind='line', y='avg_gdp', label='avg_avg_GDP', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developing_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and GDP(developing)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('GDP')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发达国家的预期寿命和卫生支出总占比随时间的变化情况  卫生支出(developed)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developed_df.plot(x='year', kind='line', y='avg_total_expenditure', label='avg_total_expenditure', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developed_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and  Total_Expenditure(developed)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('total_expenditure')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发展中国家的预期寿命和卫生支出总占比随时间的变化情况  卫生支出(developing)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developing_df.plot(x='year', kind='line', y='avg_total_expenditure', label='avg_total_expenditure', ax=ax)
    # # ax = developing_df.plot(x='year', kind='line', y='avg_percentage_expenditure', label='avg_percentage_expenditure', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developing_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and Total_Expenditure(developing)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('total_expenditure')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发达国家的预期寿命和卫生支出人均占比随时间的变化情况  卫生支出(developed)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developed_df.plot(x='year', kind='line', y='avg_percentage_expenditure', label='avg_percentage_expenditure', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developed_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and  Percentage_Expenditure(developed)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('percentage_expenditure')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发展中国家的预期寿命和卫生支出人均占比随时间的变化情况  卫生支出(developing)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developing_df.plot(x='year', kind='line', y='avg_percentage_expenditure', label='avg_percentage_expenditure', ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developing_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and Percentage_Expenditure(developing)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('percentage_expenditure')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发达国家的预期寿命和人力发展指数随时间的变化情况  (developed)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developed_df.plot(x='year', kind='line', y='avg_income_composition_of_resources', label='avg_income_composition_of_resources',
    #                        ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developed_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and Income_composition_of_resources(developed)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('income_composition_of_resources')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # # 发展中国家的预期寿命和人力发展指数随时间的变化情况  (developing)
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = developing_df.plot(x='year', kind='line', y='avg_income_composition_of_resources', label='avg_income_composition_of_resources',
    #                         ax=ax)
    # # 在右侧添加坐标轴
    # ax2 = ax.twinx()
    # ax2 = developing_df.plot(x='year', kind='line', y='avg_life', label='avg_life', ax=ax2, color='red')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('Life_expectancy and Income_composition_of_resources(developing)')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('income_composition_of_resources')
    # ax2.set_ylabel('life')
    # plt.show()
    #
    # query_hql = 'select Life_expectancy as life_expectancy, Total_expenditure as total_expenditure from life'
    # df = hive_read_hql(query_hql, conn)
    #
    # # 去除df中包含空值的行
    # df = df.dropna(axis=0, how='any')
    # # 筛选出life_expectancy<65的行
    # df = df[df['life_expectancy'] < 65]
    # # 绘制关于life_expectancy和total_expenditure的散点图
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax.scatter(df['life_expectancy'], df['total_expenditure'])
    # # 添加标题
    # plt.title('Life_expectancy and Total_Expenditure')
    # # 添加坐标轴标签
    # ax.set_xlabel('life_expectancy')
    # ax.set_ylabel('total_expenditure')
    # plt.show()
    #
    # query_hql = 'select avg(Life_expectancy) as life_expectancy, avg(BMI) as bmi, avg(GDP) as gdp, Year as year from part3 group by year'
    # df = hive_read_hql(query_hql, conn)
    #
    # # 绘制关于life_expectancy,bmi,gdp随时间的变化情况
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax = df.plot(x='year', kind='line', y='bmi', label='bmi', ax=ax)
    # ax2 = ax.twinx()
    # ax2 = df.plot(x='year', kind='line', y='gdp', label='gdp', ax=ax2, color='green')
    # # 添加图例
    # plt.legend(loc='best')
    # # 添加标题
    # plt.title('BMI and GDP')
    # # 添加坐标轴标签
    # ax.set_xlabel('year')
    # ax.set_ylabel('bmi')
    # ax2.set_ylabel('gdp')
    # plt.show()
    #
    # query_hql = 'select Life_expectancy as life_expectancy, schooling as schooling from life'
    # df = hive_read_hql(query_hql, conn)
    #
    # # 去除df中包含空值的行
    # df = df.dropna(axis=0, how='any')
    # # 绘制关于life_expectancy和schooling的散点图
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax.scatter(df['life_expectancy'], df['schooling'])
    # # 添加标题
    # plt.title('Life_expectancy and Schooling')
    # # 添加坐标轴标签
    # ax.set_xlabel('life_expectancy')
    # ax.set_ylabel('schooling')
    # plt.show()
    #
    # query_hql = 'select Life_expectancy as life_expectancy, population as population from life where population < 1126135777'
    # df = hive_read_hql(query_hql, conn)
    #
    # # 去除df中包含空值的行
    # df = df.dropna(axis=0, how='any')
    # # 获取到population的最大值和最小值
    # max_population = df['population'].max()
    # min_population = df['population'].min()
    # print(max_population, min_population)
    # # 设置分组区间
    # bins = np.linspace(min_population, max_population, 50)
    # # 纵坐标表示population的区间内，life_expectancy的平均值
    # # 横坐标表示population的区间
    # df['population_bins'] = pd.cut(df['population'], bins)
    # df = df.groupby('population_bins')['life_expectancy'].mean()
    # # 绘制关于life_expectancy和population的柱状图
    # fig = plt.figure(figsize=(12, 8))
    # ax = fig.add_subplot(1, 1, 1)
    # df.plot(kind='bar', ax=ax)
    # # 添加标题
    # plt.title('Life_expectancy and Population')
    # # 添加坐标轴标签
    # ax.set_xlabel('population')
    # ax.set_ylabel('life_expectancy')
    # plt.show()
    #
    # query_hql = 'select Life_expectancy as life_expectancy, population as population from life where population < 1126135777'
    # df = hive_read_hql(query_hql, conn)
    #
    # # 去除df中包含空值的行
    # df = df.dropna(axis=0, how='any')
    # # 绘制关于life_expectancy和population的散点图
    # fig = plt.figure(figsize=(6, 4))
    # ax = fig.add_subplot(1, 1, 1)
    # ax.scatter(df['population'], df['life_expectancy'])
    # # 添加标题
    # plt.title('Life_expectancy and Population')
    # # 添加坐标轴标签
    # ax.set_ylabel('life_expectancy')
    # ax.set_xlabel('population')
    # plt.show()
    #
    query_hql = 'select Life_expectancy as life_expectancy,adult_mortality as adult_mortality, Status as status, Year as year  from life'
    df = hive_read_hql(query_hql, conn)

    # 去除df中包含空值的行
    df = df.dropna(axis=0, how='any')
    # 筛选出status为Developed的行
    df = df[df['status'] == 'Developed']
    # 绘制关于life_expectancy和adult_mortality的散点图
    fig = plt.figure(figsize=(6, 4))
    ax = fig.add_subplot(1, 1, 1)
    ax.scatter(df['adult_mortality'], df['life_expectancy'])
    # 添加标题
    plt.title('Life_expectancy and Adult_mortality')
    # 添加坐标轴标签
    ax.set_xlabel('adult_mortality')
    ax.set_ylabel('life_expectancy')
    plt.show()
    pass


if __name__ == '__main__':
    # hive_connect()
    data_exp()
    pass
